using System;
using System.Collections;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

using LocalBoard.Domain;

namespace LocalBoard.Persistence.SqlAccessor
{
	/// <summary>
	/// Summary description for SqlBoardAccessor.
	/// </summary>
	public class SqlBoardAccessor : SqlDataAccessBase, IBoardAccessor
	{
		public static readonly SqlBoardAccessor Instance = new SqlBoardAccessor();

		private SqlBoardAccessor()
		{
		}

		#region IBoardAccessor Members

		public LocalBoard.Domain.Board Get(int id)
		{
			SqlDataReader dataReader = null;
			try
			{
				dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetBoardById", id);
				// Execute the stored procedure
				if(dataReader.Read())
					return GetBoardFromResultRow(dataReader);
			}
			catch (Exception ex )
			{
				// Log error details
				throw ex;
			}
			finally
			{
				if(dataReader != null) dataReader.Close();
			}
			return null;
		}

		private static Board GetBoardFromResultRow(SqlDataReader dataReader)
		{
			SqlProfileAccessor userAccessor = SqlProfileAccessor.Instance;
			Board board = new Board(DataConverter.GetInteger(dataReader["Id"]), DataConverter.GetString(dataReader["Name"]), userAccessor.Get(DataConverter.GetInteger(dataReader["Manager_Id"])), DataConverter.GetInteger(dataReader["AccessRole_Id"]), DataConverter.GetInteger(dataReader["PostRole_Id"]), DataConverter.GetInteger(dataReader["Club_Id"]), Board.GetBoardViewType(DataConverter.GetInteger(dataReader["ViewType"])));
			board.Address = DataConverter.GetString(dataReader["Address"]);
			board.City = DataConverter.GetString(dataReader["City"]);
			board.Latitude = DataConverter.GetString(dataReader["Latitude"]);
			board.Longitude = DataConverter.GetString(dataReader["Longitude"]);
			board.PlaceName = DataConverter.GetString(dataReader["PlaceName"]);
			board.PostalCode = DataConverter.GetString(dataReader["PostalCode"]);
			board.Region = DataConverter.GetString(dataReader["Region"]);
			board.State = DataConverter.GetString(dataReader["State"]);
			return board;
		}

		public System.Collections.ArrayList GetBoardListByClubId(int clubId)
		{
			SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetBoardListByClubId", clubId);

			ArrayList boardList = new ArrayList();
			SqlProfileAccessor userAccessor = SqlProfileAccessor.Instance;

			while(dataReader.Read())
			{
				boardList.Add(GetBoardFromResultRow(dataReader));
			}
			dataReader.Close();
			return boardList;
		}

		public int Insert(LocalBoard.Domain.Board board)
		{
			return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, "InsertBoard", board.Name, (board.Manager != null) ? board.Manager.Id : 0, board.AccessRoleId, board.PostRoleId, board.ClubId, Board.GetBoardViewTypeId(board.ViewType), board.DataXml.OuterXml));
		}

		public void Update(LocalBoard.Domain.Board board)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "UpdateBoard", board.Id, board.Name, (board.Manager != null) ? board.Manager.Id : 0, board.AccessRoleId, board.PostRoleId, Board.GetBoardViewTypeId(board.ViewType), board.DataXml.OuterXml);
		}

		public void Delete(int id)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "DeleteBoard", id);
		}

		public void AssignManagerToBoard(int profileId, int id)
		{
			Board targetBoard = this.Get(id);
			if(targetBoard != null)
			{
				Profile manager = SqlProfileAccessor.Instance.Get(profileId);
				if(manager != null)
				{
					if(!SqlClubProfileAccessor.Instance.IsUserInTheClub(profileId, targetBoard.ClubId))
						throw new Exception(string.Format("User id, {0} is not a member of the club id, {1}", profileId, targetBoard.ClubId));
					targetBoard.Manager = manager;
					this.Update(targetBoard);
					return;
				}
			}
			throw new Exception(string.Format("prameter, {0} is not valid.", (targetBoard == null)?"id":"profileId"));
		}

		#endregion
	}
}
